DDE - Getting data into Microsoft Excel

Modified on 2018/01/22 14:51 by Ryan Byrne (CTS) — Categorized as: Uncategorized

IMPORTANT: In order to pull data via DDE from T4, you first need to have T4 open and subscribed to the desired markets. A subscription can be made by adding the desired markets to a quote board. It is good practice to open T4 first, then open Excel; and to close Excel first, then close T4.

The DDE link format for market data in Excel is:

=T4Screen|'MKT_<marketid>'!<value>

Where:

<marketid> is the unique identifier for the market in T4. This does not match any exchange published symbol. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.

<value> is the data value of the market that you want, for example last trade price. The valid values are shown below:

ValueMeaning
DescriptionThe name of the market.
NumeratorThe smallest amount that the price of a market can move by, in ticks.
DenominatorThe denominator used for converting ticks into price and vice-versa.
Settlement_Price,Settlement_TicksThe last settlement price received.
Open_Price,Open_TicksThe opening trade price for the day.
High_Price,High_TicksThe highest traded price for the day.
Low_Price,Low_TicksThe lowest traded price for the day.
ModeThe current market state, e.g. PreOpen, Open, Closed etc.
Total_Traded_VolumeThe total volume traded by this market today.
Last_Trade_VolumeThe last volume traded at the current price. If the frontend is set to show the total traded at this price since the last price changed then that value will be sent, otherwise the volume just traded will be sent.
Last_Trade_Price,Last_Trade_TicksThe price of the last trade to occur.
Bid_VolumeThe volume of the current best bid.
Bid_Price,Bid_TicksThe price of the current best bid.
Offer_VolumeThe volume of the current best offer.
Offer_Price,Offer_TicksThe price of the current best offer.
Net_Change_Price,Net_Change_TicksThe net change from the last settlement price to the last trade price.


The last trade information will include trades in outrights due to spreads trading if the frontend is configured to show that information itself.

The Bid and Offer information will include implied prices if the frontend is configured to show it.

The difference between the ‘_TICKS’ and ‘_PRICE’ values is that the ‘_PRICE’ values are display prices (what you see on the frontend, e.g. 30yr bond = 11215) and the ‘_TICKS’ values are the decimal equivalent that can have math done on it (e.g. 30yr bond = 11215 = 112 points + 15/32 = expressed as a decimal is (112 + (15/32) = 112.46875). Please note that the decimal point is not present for all markets and must be added in programmatically if desired.

To send account and position data you can use the same method as above, except using the Account window instead of the Quote Board. Note that if you trade in a new market then Excel will not automatically detect that position, you must place links in Excel for each specific position that you want.

The DDE link format for account data in Excel is:

=T4Screen|'ACT_<accountid>'!<value>

Where:

<accountid> is the unique identifier for the account in T4. This does not match the account number or name. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.

<value> is the data value of the account that you want, for example P&L. The valid values are shown below:

ValueMeaning
DescriptionThe name of the account.
AccountThe account number.
FirmThe firm this account belongs to.
StatusThe status of this account, e.g. OK, Blocked etc.
PLThe total P&L of all the positions for this account
UPLThe total unrealized P&L of all the positions for this account.
RPLThe total realized P&L of all the positions for this account.
Overnight_UPLThe total unrealized P&L due to fills carried over from the previous trading day of all the positions for this account.
CashThe amount of available cash the account has available to trade with. Takes into consideration the Balance, P&L and Margin.
Net_EquityThe balance of the account if all positions were closed. Takes into consideration the Balance and P&L.
MarginThe total margin requirement for all the positions for this account.
BalanceThe start of day balance of the account.


The DDE link format for position data in Excel is:

=T4Screen|'POS_<accountid>_<marketid>'!<value>

Where:

<accountid> is the unique identifier for the account in T4. This does not match the account number or name. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.

<marketid> is the unique identifier for the market in T4. This does not match any exchange published symbol. You can only obtain these by using the above method of copying the link for a value from T4 and pasting it into Excel or some other application.

<value> is the data value of the position that you want, for example P&L or net position. The valid values are shown below:

ValueMeaning
DescriptionThe name of the market this position is for.
NetThe net position of this account in this market.
PLThe total P&L of this position.
UPLThe unrealized P&L from the open position.
RPLThe realized P&L from the closed position.
Overnight_UPLThe unrealized P&L due to positions carried over from the previous trading day.
MarginThe margin requirement for this position.
BuysThe total number of contracts bought.
SellsThe total number of contracts sold.
Working_BuysThe number of buy contracts that are working.
Working_SellsThe number of sell contracts that are working.